﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

/// <summary>
/// Summary description for UtilsExcel
/// </summary>
public class UtilsExcel
{
    public DataTable GenerateExcelData(string path, List<string> param)
    {
        OleDbConnection oledbConn = null;
        try
        {
            if (Path.GetExtension(path) == ".xls")
            {
                oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
            }
            else if (Path.GetExtension(path) == ".xlsx")
            {
                oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';");
            }
            if (oledbConn == null) return null;
            oledbConn.Open();
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            DataSet ds = new DataSet();

            cmd.Connection = oledbConn;
            cmd.CommandType = CommandType.Text;
            string a = "";
            if (param.Count > 0)
            {
                foreach (string item in param)
                {
                    a += "[" + item + "]" + ",";
                }
                a = a.Remove(a.Length - 1);
                cmd.CommandText = "SELECT " + a + " FROM [Sheet1$]";
                oleda = new OleDbDataAdapter(cmd);
                oleda.Fill(ds);
                return ds.Tables[0];
            }
            else return null;

        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            oledbConn.Close();
        }
    }
    public static void ExportToExcel(string hk, DataSet b_ds, string filename, HttpResponse response)
    {

        DataTable b_dt = new DataTable();
        b_dt = b_ds.Tables[0];
        DataTable b_dt_mon = b_ds.Tables[1];
        var workbook = new HSSFWorkbook();
        NpoiHelper npoiHelper = new NpoiHelper(workbook);

        ICell cell = null;
        IRow row = null;
        ISheet sheet = null;
        int rowIndex = 0;
        int column_count = b_dt.Columns.Count;

        sheet = workbook.CreateSheet("Sheet1");

        //Line 1
        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        npoiHelper.GetTableCell(row, 0).SetCellValue("TRƯỜNG CAO ĐẲNG MÚA VIỆT NAM");
        //Line 2
        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        npoiHelper.GetTableCell(row, 0).SetCellValue("Phòng đào tạo và công tác học sinh sinh viên");

        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        npoiHelper.GetTableCell(row, 3).SetCellValue("BẢNG ĐIỂM HỌC KỲ " + hk);
        rowIndex = rowIndex + 3;
        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        string b_ten;
        for (int i = 0; i < column_count; i++)
        {
            DataRow[] _row = b_dt_mon.Select("Id_mon='" + b_dt.Columns[i].ColumnName + "'");
            if (_row.Length == 0)
                b_ten = b_dt.Columns[i].ColumnName;
            else
                b_ten = _row[0]["TenMon"].ToString();
            npoiHelper.GetTableHeaderCenter(row, i).SetCellValue(b_ten);
        }

        foreach (DataRow dataRow in b_dt.Rows)
        {
            row = NpoiHelper.GetRow(sheet, ++rowIndex);

            for (int i = 0; i < column_count; i++)
            {
                if (b_dt.Columns[i].DataType.Name == "Decimal")
                {
                    if (dataRow[i].ToString() == "")
                    {
                        npoiHelper.GetTableCellRight(row, i).SetCellValue(0.0);
                    }
                    else
                    {
                        npoiHelper.GetTableCellRight(row, i).SetCellValue(Double.Parse(dataRow[i].ToString()));
                    }
                }
                else
                    npoiHelper.GetTableCell(row, i).SetCellValue(dataRow[i].ToString());
            }
        }
        ToExcel(workbook, filename, response);
    }
    public static void ToExcel(HSSFWorkbook workbook, string fileName, HttpResponse response)
    {
        // Save the Excel spreadsheet to a file on the web server's file system
        using (var fileData = new FileStream(fileName, FileMode.Create))
        {
            workbook.Write(fileData);
        }

        using (var exportData = new MemoryStream())
        {
            workbook.Write(exportData);
            string saveAsFileName = string.Format(fileName + "-{0:d}.xls", DateTime.Now).Replace("/", "-");
            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
            response.Clear();
            response.BinaryWrite(exportData.GetBuffer());
            //response.End();
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
    }
}